Format Function
Returns as a String a formatted version of the number passed based on the parameters specified. The Format function is similar to the way spreadsheet applications format numbers.
Syntax
result = Format( number, formatSpec )
| Parameters | ||
|
number |
The number to be formatted. |
|
|
formatSpec |
Defines the formatting to be applied to the number passed. |
|
Format will use the information based on the user's locale even if the user's locale is a Unicode-only locale.
Notes
The formatSpec is a string made up of one or more special characters that control how the number will be formatted:
| Character | Description |
| # | Placeholder that displays the digit from the value if it is present. |
| 0 | Placeholder that displays the digit from the value if it is present. If no digit is present, 0 (zero) is displayed in its place. |
| . | Placeholder for the position of the decimal point. |
| , | Placeholder that indicates that the number should be formatted with thousands separators. |
| % | Displays the number multiplied by 100. |
| ( | Displays an open paren. |
| ) | Displays a closing paren. |
| + | Displays the plus sign to the left of the number if the number is positive or a minus sign if the number is negative. |
| - | Displays a minus sign to the left of the number if the number is negative. There is no effect for positive numbers. |
| E or e | Displays the number in scientific notation. |
| \character | Displays the character that follows the backslash. |
The absolute value of the number is displayed. You must use the + or - signs if you want the sign displayed.
Although the special formatting characters are U.S. characters, the actual characters that will appear are based on the current operating system settings. For example, Windows uses the settings in the user's Regional and Language Options Control Panel. Formatting characters are specified in similar ways on other operating systems.
The formatSpec can be made up of up to three formats separated by semicolons. The first format is the format to be used for positive numbers. The second format is the format to be used for negative numbers and the third format is the format to be used for zero.
Examples
The following are several examples that use the various special formatting characters.
| Format | Number | Formatted String |
| #.## | 1.784 | 1.78 |
| #.0000 | 1.3 | 1.3000 |
| 0000 | 5 | 0005 |
| #% | 0.25 | 25% |
| ###,###.## | 145678.5 | 145,678.5 |
| #.##e+ | 145678.5 | 146e+5 |
| -#.## | -3.7 | -3.7 |
| +#.## | 3.7 | +3.7 |
| #.##;(#.##);\z\e\r\o | 3.7 | 3.7 |
| #.##;(#.##);\z\e\r\o | -3.7 | (3.7) |
| #.##;(#.##);\z\e\r\o | 0 | zero |
The following example returns the number 3560.3 formatted as $3,560.30.